package com.aixin.csopuser.repository;

import com.aixin.csopuser.domain.CustomerDomain;
import com.aixin.csopuser.domain.CustomereEuity;
import com.aixin.csopuser.domain.vo.PointHistory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.Map;
import java.util.Optional;

public interface CustomerRepository extends JpaRepository<CustomerDomain,Long>, JpaSpecificationExecutor<CustomerDomain> {

    Optional<CustomerDomain> findByCustomerID(String customerID);

    @Query(value = "SELECT * FROM customerequity where customerID = :customerID and equityType = 0 ",nativeQuery = true)
    List<Map<String,String>> getEquity(@Param("customerID") String customerID);



    @Query(value = "SELECT createTime,txnContent,txnPoint,pointType FROM pointcontxnhist WHERE customerID=:customerID " +
            "UNION ALL " +
            "SELECT createTime,txnContent,txnPoint,pointType FROM pointinctxnhist WHERE customerID=:customerID " +
            " LIMIT 5",nativeQuery = true)
    List<Map<String,String>> getPoint(@Param("customerID") String customerID);

    @Query(nativeQuery = true, value = "select con.createTime,con.txnContent,con.txnPoint,con.pointType " +
            "from pointcontxnhist con where con.customerID=:customerID " +
            "union all " +
            "select inc.createTime,inc.txnContent,inc.txnPoint,inc.pointType " +
            "from pointinctxnhist inc where inc.customerID=:customerID order by createTime desc limit 5")
    List<Map<String, Object>> getCustomerPointHistory(@Param("customerID") String customerID);

//    @Query(value = "select * from customerownedservices cws LEFT JOIN servicedef  sd ON cws.serviceID= sd.serviceID WHERE cws.customerID=:customerID AND cws.type= :type " +
//            "and (:equityLevelID is  null or cws.equityName = :equityLevelID)" +
//            "and (:policyID is  null or cws.policyID = :policyID)",nativeQuery = true)
//    List getService(@Param("customerID") String customerID,@Param("type") String type,@Param("equityLevelID") String equityID,@Param("policyID") String policyID);

    @Query(value = "select * from customerownedservices cws LEFT JOIN servicedef  sd ON cws.serviceID= sd.serviceID WHERE cws.customerID=:customerID " +
            "and (:equityLevelID is  null or cws.equityLevelID = :equityLevelID)" +
            "and (:policyID is  null or cws.policyID = :policyID)",nativeQuery = true)
    List getService(@Param("customerID") String customerID,@Param("equityLevelID") String equityID,@Param("policyID") String policyID);

    @Query(value = "select new CustomerDomain(c.customerID, c.custName, c.phoneNumber, c.certID, c.gender, c.firstEffTime) " +
            "from CustomerDomain c " +
            "join CustomerEquity ce on ce.customerID=c.customerID " +
            "left join ServiceLevelDef sld on sld.equityID=ce.equityID and sld.equityLevelID=ce.custLevelID " +
            "where 1=1 " +
            "and (:custName is null or c.custName like concat('%', :custName, '%')) " +
            "and (:certID is null or c.certID like concat('%', :certID, '%')) " +
            "and (:phoneNumber is null or c.phoneNumber like concat('%', :phoneNumber, '%')) " +
            "and (:serviceID is null or sld.serviceID=:serviceID) " +
            "and (:equityLevelID is null or ce.custLevelID=:equityLevelID) " +
            "and (:equityType is null or ce.equityType=:equityType) " +
            "")
    Page<CustomerDomain> queryCustomers(@Param("custName") String custName,
                                        @Param("certID") String certID,
                                        @Param("phoneNumber") String phoneNumber,
                                        @Param("serviceID") String serviceID,
                                        @Param("equityLevelID") String equityLevelID,
                                        @Param("equityType") Integer equityType,
                                        Pageable pageable);

    @Query(value = "select c " +
            "from CustomerDomain c " +
            "join CustomerEquity ce on ce.customerID=c.customerID " +
            "left join ServiceLevelDef sld on sld.equityID=ce.equityID and sld.equityLevelID=ce.custLevelID " +
            "where 1=1 " +
            "and (:custName is null or c.custName like concat('%', :custName, '%')) " +
            "and (:certID is null or c.certID like concat('%', :certID, '%')) " +
            "and (:phoneNumber is null or c.phoneNumber like concat('%', :phoneNumber, '%')) " +
            "and (:serviceID is null or sld.serviceID=:serviceID) " +
            "and (:equityLevelID is null or ce.custLevelID=:equityLevelID) " +
            "and (:equityType is null or ce.equityType=:equityType) " +
            "")
    Page<CustomerDomain> clientCustomers(@Param("custName") String custName,
                                        @Param("certID") String certID,
                                        @Param("phoneNumber") String phoneNumber,
                                        @Param("serviceID") String serviceID,
                                        @Param("equityLevelID") String equityLevelID,
                                        @Param("equityType") Integer equityType,
                                        Pageable pageable);

//    @Query(value = "select new CustomerDomain(c.customerID, c.custName, c.phoneNumber, c.certID, c.gender, c.firstEffTime) " +
//            "from CustomerDomain c " +
//            "where 1=1 " +
//            "and (:custName is null or c.custName like concat('%', :custName, '%')) " +
//            "and (:certID is null or c.certID like concat('%', :certID, '%')) " +
//            "and (:phoneNumber is null or c.phoneNumber like concat('%', :phoneNumber, '%')) ")
//    Page<CustomerDomain> queryCustomers(@Param("custName") String custName,
//                                        @Param("certID") String certID,
//                                        @Param("phoneNumber") String phoneNumber,
//                                        Pageable pageable);
}
